#! /usr/bin/env python
#coding=utf-8
#from uliweb import expose
#from sqlalchemy import *
#from uliweb.orm import *
#from uliweb.utils.pyini import Ini
#from sqlalchemy.sql import select
#from sqlalchemy.sql import and_, or_, not_
#
#import time, sys
#sys.path.insert(0, '../apps')
#from departments.models import *
#from charts.models import *
#
#import datetime
#from datetime import date
from get_month_quarter import *

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()
#meta = MetaData(db)
#db.metadata.reflect()

def run():

    #准开发需求相关报表数据处理
    req = db.metadata.tables['requirement']                                                        
    #tx_table = db.metadata.tables['requirments_tx_count']                      

    #获取本月月初和月末时间
    month_first_day,month_last_day,month_view = get_month()


    #计算本月新增准开发需求任务数，算法：开始日期小于或等于本月末and大于等于本月1日
    new = select([req.c.tx,func.count(req.c.req_id)],
                  and_(req.c.created_date>=month_first_day,
                    req.c.created_date<=month_last_day)).group_by(req.c.tx)
    #续建准开发需求任务计算算法：排除修改日期大于本月1日且状态为完成或异常终止的记录
    #即：（创建日期<小于本月1日 and 修改日期小于小于本月1日 and 状态不为完
    #成或异常终止）or （创建日期<小于本月1日 and  修改日期大于等于本月1日小于本月月末日期）)
    old = select([req.c.tx,func.count(req.c.req_id)],
                or_(and_(req.c.created_date<month_first_day,
                         req.c.modified_date<month_first_day,
                        ~req.c.stage.in_(['19','1A','FN'])),
                    and_(req.c.created_date<month_first_day,
                         req.c.modified_date<=month_last_day,
                         req.c.modified_date>=month_first_day))).group_by(req.c.tx)

    #执行select查询语句
    new_rs = new.execute()
    old_rs = old.execute()

    #print new
    #print old
    #初始化data
    data = {}

    #从tx表获取tx_id
    req_tx_model = get_model('tx')
    sel_tx = select([req_tx_model.c.id])
    tx_dic = sel_tx.execute()
    #初始化data
    for stages in tx_dic:
        tx, = stages
        y = 'new_num_%d' % tx
        data[y] = 0
        y = 'old_num_%d' % tx
        data[y] = 0
        
    data['month_date'] = month_last_day
    data['month'] = month_view
    data['total_num'] = 0
    #print data    

    #初始化准开发需求总数
    total_count = 0;

    #以下两个for语句完成new_rs，old_rs中新增和续建准开发需求放在一条记录中                                                            
        
    for row in new_rs:
            tx,count = row
            table_row = 'new_num_%02d' % tx
            data[table_row] = count
            total_count +=count        
    for row in old_rs:
            tx,count = row
            table_row = 'old_num_%02d' % tx
            data[table_row] = count
            total_count +=count

    #将本月准开发需求汇总数据赋值给total_num
    data['total_num'] = total_count
    #print data
    req_model = get_model('requirement_month_report_tx')

    #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
    #db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
    req_model.remove(req_model.c.month_date == month_last_day)
    #print data
    r = req_model(**data)
    r.save()

if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise 

    
        
    
    